

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_tb_svcorder]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_tb_svcorder]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.v_tb_svcorder
AS
SELECT CAST(0 AS bit) AS choice, m.bcode AS m_bcode, m.bdate AS m_bdate, 
      m.remark AS m_remark, m.bkind AS m_bkind, m.bstate AS m_bstate, 
      m.amount AS m_Amount, 2010 AS btype, m.bid AS m_bid, m.deptid AS m_deptid, 
      m.custom1 AS m_custom1, m.custom2 AS m_custom2, m.custom3 AS m_custom3, 
      m.custom4 AS m_custom4, m.custom5 AS m_custom5, m.custom6 AS m_custom6, 
      m.custom7 AS m_custom7, m.custom8 AS m_custom8, m.custom9 AS m_custom9, 
      m.custom10 AS m_custom10, m.custom11 AS m_custom11, 
      m.custom12 AS m_custom12, m.custom13 AS m_custom13, 
      m.custom14 AS m_custom14, m.custom15 AS m_custom15, 
      m.create_by AS m_create_by, m.filid AS m_filid, m.check_by AS m_check_by, 
      cr.UI_UserName AS m_createuser, ck.UI_UserName AS m_checkuser, 
      f.FI_FilNM AS m_filname, d.*, p.code AS p_code, p.name AS p_name, 
   '' as producttypename,p.producttypeid,      p.spec AS p_spec, p.name AS productname, p.calccost AS p_calccost, 
  p.custom1 p_custom1, p.custom2 p_custom2, p.custom3 p_custom3, p.custom4 p_custom4, p.custom5 p_custom5 ,
  p.custom6 p_custom6, p.custom7 p_custom7, p.custom8 p_custom8, p.custom9 p_custom9, p.custom10 p_custom10, 
  p.custom11 p_custom11, p.custom12 p_custom12, p.custom13 p_custom13, p.custom14 p_custom14, p.custom15 p_custom15,
      p.kind AS p_kind, u.unitname, bu.unitname AS baseunitname, u.barcode, 
      au.unitname AS unitassname, e.EM_Name AS empname, w.name AS workshopname, 
      b.code AS bomcode, b.name AS bomname
FROM dbo.tb_svcorder m INNER JOIN
      dbo.tb_svcorderdtl d ON m.bid = d.bid LEFT OUTER JOIN
      dbo.ts_usersinfo cr ON m.create_by = cr.UI_UserID LEFT OUTER JOIN
      dbo.ts_usersinfo ck ON m.check_by = ck.UI_UserID LEFT OUTER JOIN
      dbo.td_filiale f ON m.filid = f.FI_FilID LEFT OUTER JOIN
      dbo.td_product p ON d.productid = p.p_id LEFT OUTER JOIN
      dbo.td_productbomver b ON d.bomid = b.bomid LEFT OUTER JOIN
      dbo.td_unitprice u ON d.unitid = u.unitid LEFT OUTER JOIN
      dbo.td_unitprice au ON d.productid = au.productid AND 
      au.u_no = 0 LEFT OUTER JOIN
      dbo.td_unitprice bu ON d.productid = bu.productid AND 
      bu.baseunit = 1 LEFT OUTER JOIN
      dbo.td_empmaster e ON d.empid = e.EM_EmployeeID LEFT OUTER JOIN
      dbo.td_workshop w ON d.workshopid = w.workshopid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

